package cn.edu.swu.lyt;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

// 需要定义Book类，或者使用正确的Book类
class Book {
    private int id;
    private String name;
    private String author;
    private double price;
    private String memo;
    private Date publish;

    public Book(int id, String name, String author, double price, String memo, Date publish) {
        this.id = id;
        this.name = name;
        this.author = author;
        this.price = price;
        this.memo = memo;
        this.publish = publish;
    }

    // Getters
    public int getId() { return id; }
    public String getName() { return name; }
    public String getAuthor() { return author; }
    public double getPrice() { return price; }
    public String getMemo() { return memo; }
    public Date getPublish() { return publish; }
}

public class JdbcTest {

    public void excute(String sql) throws SQLException {
        String dbUrl = "jdbc:mysql://10.122.7.154:3306/book_store_2025";
        String dbUser = "root";
        String dbPass = "qwer1234";

        try (Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPass)) {
            try (Statement statement = connection.createStatement()) {
                statement.execute(sql);
            }
        }
    }

    public static void main(String[] args) throws SQLException {
        JdbcTest test = new JdbcTest();

        // 第一次查询
        String sql1 = "select id,name,author,price,memo,publish from book where price < 40";
        List<Book> books1 = test.query(sql1);
        test.printBooks(books1);

        // 插入新书籍
        String insertSQL = "insert into book (name,author,price,memo,publish) values ('%s','%s','%s','%s','%s')";
        test.excute(String.format(insertSQL,"三体-黑暗森林","大刘",100,"三体-黑暗森林三体-黑暗森林","2020-09-12"));
        test.excute(String.format(insertSQL,"三体-死神永生","大刘",100,"三体-死神永生","2020-09-12"));

        // 打折操作
        String discountSQL = "update book set price = price * %s";
        test.excute(String.format(discountSQL,0.85));

        // 更新作者名
        String updateNameSQL = "update book set author = '%s' where author = '%s'";
        test.excute(String.format(updateNameSQL,"刘慈欣","大刘"));

        // 删除书籍
        String deleteSQL = "delete from book where id = %s";
        test.excute(String.format(deleteSQL,2));

        // 最终查询
        String sql2 = "select id,name,author,price,memo,publish from book";
        List<Book> books2 = test.query(sql2);
        test.printBooks(books2);
    }

    public List<Book> query(String sql) throws SQLException {
        String dbUrl = "jdbc:mysql://10.122.7.154:3306/book_store_2025";
        String dbUser = "root";
        String dbPass = "qwer1234";

        try (Connection connection = DriverManager.getConnection(dbUrl, dbUser, dbPass)) {
            try (Statement statement = connection.createStatement()) {
                ResultSet resultSet = statement.executeQuery(sql);

                List<Book> books = new ArrayList<>();
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String name = resultSet.getString("name");
                    String author = resultSet.getString("author");
                    double price = resultSet.getDouble("price");
                    String memo = resultSet.getString("memo");
                    Date publish = resultSet.getDate("publish");

                    Book book = new Book(id, name, author, price, memo, publish);
                    books.add(book);
                }
                return books;
            }
        }
    }

    public void printBooks(List<Book> books) {
        books.forEach(b -> {
            String strBook = String.format("%s,%s,%s,%s,%s,%s",
                    b.getId(), b.getName(), b.getAuthor(), b.getPrice(), b.getMemo(), b.getPublish());
            System.out.println(strBook);
        });
    }
}